package com.cap.bts.crmdp.dealer.remind.dao.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import javax.persistence.EntityManagerFactory;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.jpa.support.JpaDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.cap.bts.commonfunc.common.util.UserUtil;
import com.cap.bts.crmdp.dealer.remind.dao.IRemindDao;
import com.cap.bts.crmdp.dealer.remind.dao.IServiceRemindDao;
import com.cap.bts.crmdp.dealer.remind.dao.ITrCustomerGroupDao;
import com.cap.bts.crmdp.dealer.remind.domain.TrCustomerGroup;
import com.cap.bts.crmdp.dealer.remind.dto.RemindDto;
import com.cap.bts.crmdp.dealer.remind.dto.ServiceRemindForMemberDto;
import com.cap.bts.crmdp.dealer.remind.dto.ServiceRemindHistoryDto;
import com.cap.bts.crmdp.dealer.remind.dto.ServiceTypeDto;
import com.cap.bts.framework.codemapping.dao.ICodeMappingDao;
import com.cap.bts.framework.codemapping.domain.CodeInfo;
import com.cap.bts.framework.common.dao.callback.NativeQueryBySqlJpaCallback;
import com.cap.bts.framework.common.dao.callback.NativeQueryWithPagingJpaCallback;
import com.cap.bts.framework.common.exception.AppException;
import com.cap.bts.framework.common.util.Utils;


@Repository("remindDaoImpl")
public class RemindDaoImpl extends JpaDaoSupport implements IRemindDao {

    private static final Logger LOGGER = Logger.getLogger(RemindDaoImpl.class);

    @Autowired
    ICodeMappingDao codeMapping;
    
    @Autowired
    IServiceRemindDao serviceRemindDao;

    @Autowired
    private ITrCustomerGroupDao trCustomerGroupDao;
    
    @Autowired
    public RemindDaoImpl(EntityManagerFactory entityManagerFactory) {
        setEntityManagerFactory(entityManagerFactory);
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Nov 25, 2015
     * @see com.cap.bts.crmdp.dealer.remind.dao.IRemindDao#getRemindListByMemberNameOrMoblieOrCarPlate(com.cap.bts.crmdp.dealer.remind.dto.RemindDto)
     **/
    @Override
    public List<RemindDto> getRemindListByMemberNameOrMoblieOrCarPlate(RemindDto dto)
        throws AppException {
        List<RemindDto> remindDtos = new ArrayList<RemindDto>();
        List<ServiceTypeDto> tempServiceTypeDtos = dto.getServiceType();
        List<ServiceTypeDto> serviceTypeDtos = new ArrayList<ServiceTypeDto>();
        StringBuilder sql = new StringBuilder();
        /*sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, count(c.create_date) times,a.last_calc_date from vt_service_remind a "
            + " left join tm_service_remind_send_history c on a.id=c.service_remind_id  and c.status='1'  and c.dealer_id='"
            + UserUtil.getCurrentUser().getDealerId()+"'");*/
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item_name,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, " +
            " (select count(*) from tm_service_remind_send_history c where c.service_remind_id = a.id and c. STATUS = '1' ");
       
       SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        
        Calendar c = Calendar.getInstance();
    	c.add(Calendar.MONTH, 0);
    	//设置为1号,当前日期既为本月第一天
    	c.set(Calendar.DAY_OF_MONTH,1);
    	String dateNowFirst = formatter.format(c.getTime());
    	//获取当前月最后一天
    	Calendar ca = Calendar.getInstance();
    	ca.set(Calendar.DAY_OF_MONTH,
    		ca.getActualMaximum(Calendar.DAY_OF_MONTH));
    	String dateNowLast = formatter.format(ca.getTime());

        /*CodeInfo remindDays = codeMapping.getCodeInfoByCode("RemindDays");
        CodeInfo afterRemindDays = codeMapping.getCodeInfoByCode("AfterRemindDays");*/
        /*
        if(dto.getqRemindStartDate()!=null){
            try {
                dto.setDay(Remind Utils.days2TodayBetween(dto.getqRemindStartDate())+2);
            } catch (ParseException e) {
                throw new AppException("getRemindListByMemberNameOrMoblieOrCarPlate", e);
            }
            sql.append(" and TO_DAYS(c.create_date) >= TO_DAYS('" + dto.getqRemindStartDate() + "')");
        }else{
            sql.append(" and TO_DAYS(c.create_date) >= TO_DAYS('" + dateNowString + "') - " +remindDays.getValue());
        }
        if(dto.getqRemindEndDate()!=null){
            try {
                dto.setAfterday(RemindUtils.days2TodayBetween(dto.getqRemindEndDate())+1);
            } catch (ParseException e) {
            	throw new AppException("getRemindListByMemberNameOrMoblieOrCarPlate", e);
            }
            sql.append(" and TO_DAYS(c.create_date) <= TO_DAYS('" + dto.getqRemindEndDate() + "')");
        }else{
            sql.append(" and TO_DAYS(c.create_date) <= TO_DAYS('" + dateNowString + "') + "+afterRemindDays.getValue());
        }
        */
        sql.append(" )  times,  a.last_calc_date from vt_service_remind_wechat a  where a.dealer_num='"
            + UserUtil.getCurrentUser().getDealerId() + "' ");
        StringBuilder whereString = new StringBuilder();
        if (tempServiceTypeDtos != null) {
            for (ServiceTypeDto serviceTypeDto : tempServiceTypeDtos) {
                if (serviceTypeDto.getServiceFlag()) {
                    serviceTypeDtos.add(serviceTypeDto);
                }
            }

            if (!serviceTypeDtos.isEmpty()) {
                for (ServiceTypeDto serviceTypeDto : serviceTypeDtos) {
                    whereString.append("'" + serviceTypeDto.getServiceType() + "',");
                }
            } else {
                /*List<CodeInfo> codelist = codeMapping.getCodeInfoByType("ServiceRemind");
                if (codelist != null && !codelist.isEmpty()) {
                    for (CodeInfo ci : codelist) {

                        whereString.append("'" + ci.getValue() + "',");
                    }
                }*/
            	whereString.append("'',");
            }
            whereString.deleteCharAt(whereString.length() - 1);
            sql.append("and a.maintenance_item_name in (" + whereString.toString() + ")");
        }else{
        	List<CodeInfo> codelist=codeMapping.getCodeInfoByType("ServiceRemind");
            if(codelist!=null){
                for(CodeInfo codeinfo:codelist){
                    ServiceTypeDto serviceTypeDto=new ServiceTypeDto();
                    serviceTypeDto.setCode(codeinfo.getCode());
                    serviceTypeDto.setServiceType(codeinfo.getValue());
                    serviceTypeDtos.add(serviceTypeDto);
                }  
            }
        	if(serviceTypeDtos != null && !serviceTypeDtos.isEmpty()){
        		for (ServiceTypeDto ci : serviceTypeDtos) {
        			
        			whereString.append("'" + ci.getServiceType() + "',");
        		}
        		whereString.deleteCharAt(whereString.length() - 1);
        		sql.append("and a.maintenance_item_name in (" + whereString.toString() + ")");
        	}else{
        		sql.append("and a.maintenance_item_name in ('')");
        	}
        }
   
        if(dto.getqRemindStartDate()!=null){
            sql.append(" and TO_DAYS(a.next_calc_date) >= TO_DAYS('" + dto.getqRemindStartDate() + "')");
        }else{
            sql.append(" and TO_DAYS(a.next_calc_date) >= TO_DAYS('" + dateNowFirst + "')");
        }
        if(dto.getqRemindEndDate()!=null){
            sql.append(" and TO_DAYS(a.next_calc_date) <= TO_DAYS('" + dto.getqRemindEndDate() + "')");
        }else{
            sql.append(" and TO_DAYS(a.next_calc_date) <= TO_DAYS('" + dateNowLast + "')");
        }

        if (dto.getqMemberName() != null) {
            sql.append(" and a.member_name  like '%" + dto.getqMemberName() + "%'");
        }

        if (dto.getqCarPlate() != null) {
            sql.append(" and a.car_plate  like '%" + dto.getqCarPlate() + "%'");
        }

        if (dto.getqMobliePhone() != null) {
            sql.append(" and a.mobile_phone  like '%" + dto.getqMobliePhone() + "%'");
        }
        //判断上次保养门店是否为当前门店
        if("potentialLossFlag".equals(dto.getPotentialLossFlag())){
        	sql.append(" and a.last_maintenance_store  = '" + UserUtil.getCurrentUser().getDealerId() + "'");
        }
        if("notPotentialLossFlag".equals(dto.getPotentialLossFlag())){
        	sql.append(" and a.last_maintenance_store  != '" + UserUtil.getCurrentUser().getDealerId() + "'");
        }
        
        
        sql.append(" group by a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item_name,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage,a.last_calc_date");
        StringBuilder sqlBuilder=new StringBuilder();
        if("reSendFlag".equals(dto.getReSendFlag())){
        	sqlBuilder.append("select * from ("+sql.toString()+") x where x.times!=0"
        			+ " ORDER BY convert(x.member_name USING gb2312),convert(x.car_plate USING gb2312),x.next_calc_date");
        }
        if("notReSendFlag".equals(dto.getReSendFlag())){
        	sqlBuilder.append("select * from ("+sql.toString()+") x where x.times=0"
        			+ " ORDER BY convert(x.member_name USING gb2312),convert(x.car_plate USING gb2312),x.next_calc_date");
        }
        
        LOGGER.info("sql#############:" + sqlBuilder.toString());
        
        dto.getPager().setPageSize(10);
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryWithPagingJpaCallback<Object[]>(sqlBuilder.toString(),
                "select count(1) from (" + sqlBuilder.toString() + ") y", dto.getPager()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
                RemindDto remindDto = new RemindDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(Utils.getString(obj[1]));
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindTimes(Integer.valueOf(String.valueOf(obj[19])));
                remindDto.setLastCalcDate(String.valueOf(obj[20]));
                remindDtos.add(remindDto);
            }
        } else {
            remindDtos = null;
        }
        return remindDtos;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Nov 25, 2015
     * @see com.cap.bts.crmdp.dealer.remind.dao.IRemindDao#getRemindListByMemberNameOrMoblieOrCarPlate(com.cap.bts.crmdp.dealer.remind.dto.RemindDto)
     **/
    @Override
    public List<RemindDto> getRemindListByMemberNameOrMoblieOrCarPlateNoPage(RemindDto dto)
        throws AppException {
        List<RemindDto> remindDtos = new ArrayList<RemindDto>();
        List<ServiceTypeDto> tempServiceTypeDtos = dto.getServiceType();
        List<ServiceTypeDto> serviceTypeDtos = new ArrayList<ServiceTypeDto>();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item_name,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, count(c.create_date) times  from vt_service_remind_wechat a "
            + " left join tm_service_remind_send_history c on a.id=c.service_remind_id  and c.status='1' and c.dealer_id='"
            + UserUtil.getCurrentUser().getDealerId()
            + "' where 1=1 and a.dealer_num='"
            + UserUtil.getCurrentUser().getDealerId() + "'");
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        
        Calendar c = Calendar.getInstance();
    	c.add(Calendar.MONTH, 0);
    	//设置为1号,当前日期既为本月第一天
    	c.set(Calendar.DAY_OF_MONTH,1);
    	String dateNowFirst = formatter.format(c.getTime());
    	//获取当前月最后一天
    	Calendar ca = Calendar.getInstance();
    	ca.set(Calendar.DAY_OF_MONTH,
    		ca.getActualMaximum(Calendar.DAY_OF_MONTH));
    	String dateNowLast = formatter.format(ca.getTime());
        
        StringBuilder whereString = new StringBuilder();
        if (tempServiceTypeDtos != null) {
            for (ServiceTypeDto serviceTypeDto : tempServiceTypeDtos) {
                if (serviceTypeDto.getServiceFlag()) {
                    serviceTypeDtos.add(serviceTypeDto);
                }
            }

            if (!serviceTypeDtos.isEmpty()) {
                for (ServiceTypeDto serviceTypeDto : serviceTypeDtos) {
                    whereString.append("'" + serviceTypeDto.getServiceType() + "',");
                }
                whereString.deleteCharAt(whereString.length() - 1);
                sql.append("and a.maintenance_item_name in (" + whereString.toString() + ")");
            } else {
                List<CodeInfo> codelist = codeMapping.getCodeInfoByType("ServiceRemind");
                if (codelist != null && !codelist.isEmpty()) {
                    for (CodeInfo ci : codelist) {
                        whereString.append("'" + ci.getValue() + "',");
                    }
                whereString.deleteCharAt(whereString.length() - 1);
                sql.append("and a.maintenance_item_name in (" + whereString.toString() + ")");
                }
            }
        }
        
        if(dto.getqRemindStartDate()!=null){
            sql.append(" and TO_DAYS(a.next_calc_date) >= TO_DAYS('" + dto.getqRemindStartDate() + "')");
        }else{
            sql.append(" and TO_DAYS(a.next_calc_date) >= TO_DAYS('" + dateNowFirst + "')");
        }
        if(dto.getqRemindEndDate()!=null){
            sql.append(" and TO_DAYS(a.next_calc_date) <= TO_DAYS('" + dto.getqRemindEndDate() + "')");
        }else{
            sql.append(" and TO_DAYS(a.next_calc_date) <= TO_DAYS('" + dateNowLast + "')");
        }
        
        if (dto.getqMemberName() != null) {
            sql.append(" and a.member_name  like '%" + dto.getqMemberName() + "%'");
        }

        if (dto.getqCarPlate() != null) {
            sql.append(" and a.car_plate  like '%" + dto.getqCarPlate() + "%'");
        }

        if (dto.getqMobliePhone() != null) {
            sql.append(" and a.mobile_phone  like '%" + dto.getqMobliePhone() + "%'");
        }
        
        //判断上次保养门店是否为当前门店
        if("potentialLossFlag".equals(dto.getPotentialLossFlag())){
        	sql.append(" and a.last_maintenance_store  = '" + UserUtil.getCurrentUser().getDealerId() + "'");
        }
        if("notPotentialLossFlag".equals(dto.getPotentialLossFlag())){
        	sql.append(" and a.last_maintenance_store  != '" + UserUtil.getCurrentUser().getDealerId() + "'");
        }
        sql.append(" group by a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item_name,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage");
        StringBuilder sqlAll = new StringBuilder();
        if("reSendFlag".equals(dto.getReSendFlag())){
            sqlAll.append("select * from ("+sql.toString()+") x where x.times!=0"
        			+ " ORDER BY convert(x.member_name USING gb2312),convert(x.car_plate USING gb2312),x.next_calc_date");
        }
        if("notReSendFlag".equals(dto.getReSendFlag())){
            sqlAll.append("select * from ("+sql.toString()+") x where x.times=0"
        			+ " ORDER BY convert(x.member_name USING gb2312),convert(x.car_plate USING gb2312),x.next_calc_date");
        }
        LOGGER.info("sqlforAll#############:" + sqlAll.toString());
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sqlAll.toString()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
                RemindDto remindDto = new RemindDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(obj[1].toString());
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindTimes(Integer.valueOf(String.valueOf(obj[19])));
                remindDtos.add(remindDto);
            }

        } else {
            remindDtos = null;
        }
        return remindDtos;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Dec 2, 2015
     * @see com.cap.bts.crmdp.dealer.remind.dao.IRemindDao#getRemindHistoryList(com.cap.bts.crmdp.dealer.remind.dto.RemindDto)
     **/
    @Override
    public List<RemindDto> getRemindHistoryList(RemindDto dto) throws AppException {
        /*if (dto.getFromPage() != null && "remind".equals(dto.getFromPage())&&dto.getIsUserHistory()!=1) {
            return new ArrayList<RemindDto>();
        }*/
        List<RemindDto> remindDtos = new ArrayList<RemindDto>();
        List<ServiceTypeDto> tempServiceTypeDtos = dto.getServiceType();
        List<ServiceTypeDto> serviceTypeDtos = new ArrayList<ServiceTypeDto>();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, b.message_content,b.create_date"
            + " FROM"
            + " vt_service_remind a"
            + " INNER JOIN tm_service_remind_send_history b"
            + " where a.id=b.service_remind_id  and a.dealer_num='" + UserUtil.getCurrentUser().getDealerId() + "' ");
        
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        Calendar c = Calendar.getInstance();
    	c.add(Calendar.MONTH, 0);
    	//设置为1号,当前日期既为本月第一天
    	c.set(Calendar.DAY_OF_MONTH,1);
    	String dateNowFirst = formatter.format(c.getTime());
    	//获取当前月最后一天
    	Calendar ca = Calendar.getInstance();
    	ca.set(Calendar.DAY_OF_MONTH,
    		ca.getActualMaximum(Calendar.DAY_OF_MONTH));
    	String dateNowLast = formatter.format(ca.getTime());
    	
        StringBuilder whereString = new StringBuilder();
        
        // 单独用户查询
        if (dto.getIsUserHistory() != null && dto.getIsUserHistory() == 1) {
            sql.append("and a.maintenance_item in ('" + dto.getqHServiceType() + "')");

            if (dto.getqHMemberName() != null &&StringUtils.hasText(dto.getqHMemberName())) {
                sql.append(" and a.member_name  like '%" + dto.getqHMemberName() + "%'");
            }

            if (dto.getqHCarPlate() != null&&StringUtils.hasText(dto.getqHCarPlate())) {
                sql.append(" and a.car_plate  like '%" + dto.getqHCarPlate() + "%'");
            }

            if (dto.getqHMobliePhone() != null&&StringUtils.hasText(dto.getqHMobliePhone())) {
                sql.append(" and a.mobile_phone  like '%" + dto.getqHMobliePhone() + "%'");
            }
          /**
           *   //日期条件筛选
            SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            // X天后日期
            java.util.Date myDate = new java.util.Date();
            String dateNowString = formatter.format(myDate);

            CodeInfo remindDays = codeMapping.getCodeInfoByCode("RemindDays");
            CodeInfo afterRemindDays = codeMapping.getCodeInfoByCode("AfterRemindDays");
            
            if (dto.getDay() != null) {
                Long days = dto.getDay();
                myDate.setTime(((new java.util.Date().getTime() / 1000) + 60 * 60 * 24 * days) * 1000);
                String dateString = formatter.format(myDate);
                sql.append(" and TO_DAYS(a.next_calc_date) <= TO_DAYS('" + dateString + "')");
                sql.append(" and TO_DAYS(b.create_date) <= TO_DAYS('" + dateString + "')");
            } else {
                sql.append(" and TO_DAYS(a.next_calc_date) <= TO_DAYS('" + dateNowString + "') + "
                    + remindDays.getValue());
                sql.append(" and TO_DAYS(b.create_date) <= TO_DAYS('" + dateNowString + "') + "
                    + remindDays.getValue());
            }
            if (dto.getAfterday() != null) {
                sql.append(" and TO_DAYS(a.next_calc_date) + " + dto.getAfterday() + " >= TO_DAYS('"
                    + dateNowString + "')");
                sql.append(" and TO_DAYS(b.create_date) + " + dto.getAfterday() + " >= TO_DAYS('"
                    + dateNowString + "')");
            } else {
                sql.append(" and TO_DAYS(a.next_calc_date) >= TO_DAYS('" + dateNowString + "') - "
                    + afterRemindDays.getValue());
                sql.append(" and TO_DAYS(b.create_date) >= TO_DAYS('" + dateNowString + "') - "
                    + afterRemindDays.getValue());
            }
            *
            **/
            
            sql.append(" order by b.create_date desc");
        } else {
            if (tempServiceTypeDtos != null) {
                for (ServiceTypeDto serviceTypeDto : tempServiceTypeDtos) {
                    if (serviceTypeDto.getServiceFlag()) {
                        serviceTypeDtos.add(serviceTypeDto);
                    }
                }

                /*if (!serviceTypeDtos.isEmpty()) {
                    for (ServiceTypeDto serviceTypeDto : serviceTypeDtos) {
                        whereString.append("'" + serviceTypeDto.getServiceType() + "',");
                    }
                    whereString.deleteCharAt(whereString.length() - 1);
                    sql.append("and a.maintenance_item in (" + whereString.toString() + ")");
                }*/
                
                if (!serviceTypeDtos.isEmpty()) {
                    for (ServiceTypeDto serviceTypeDto : serviceTypeDtos) {
                        whereString.append("'" + serviceTypeDto.getServiceType() + "',");
                    }
                    whereString.deleteCharAt(whereString.length() - 1);
                    sql.append("and a.maintenance_item in (" + whereString.toString() + ")");
                } else {
                    List<CodeInfo> codelist = codeMapping.getCodeInfoByType("ServiceRemind");
                    if (codelist != null && !codelist.isEmpty()) {
                        for (CodeInfo ci : codelist) {
                            whereString.append("'" + ci.getValue() + "',");
                        }
                    whereString.deleteCharAt(whereString.length() - 1);
                    sql.append("and a.maintenance_item in (" + whereString.toString() + ")");
                    }
                }
            }

       /**
        *      SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
            // X天后日期
            java.util.Date myDate = new java.util.Date();
            String dateNowString = formatter.format(myDate);

            CodeInfo afterRemindDays = codeMapping.getCodeInfoByCode("AfterRemindDays");

            sql.append(" and TO_DAYS(a.next_calc_date) < TO_DAYS('" + dateNowString + "') - "
                + afterRemindDays.getValue());*
                **/

            if (dto.getqHMemberName() != null&&StringUtils.hasText(dto.getqHMemberName())) {
                sql.append(" and a.member_name  like '%" + dto.getqHMemberName() + "%'");
            }

            if (dto.getqHCarPlate() != null&&StringUtils.hasText(dto.getqHCarPlate())) {
                sql.append(" and a.car_plate  like '%" + dto.getqHCarPlate() + "%'");
            }

            if (dto.getqHMobliePhone() != null &&StringUtils.hasText(dto.getqHMobliePhone())) {
                sql.append(" and a.mobile_phone  like '%" + dto.getqHMobliePhone() + "%'");
            }

            if (dto.getqStartDate() != null&&StringUtils.hasText(dto.getqStartDate())) {
                sql.append(" and TO_DAYS(b.create_date) >= TO_DAYS('" + dto.getqStartDate() + "')");
            }else{
            	sql.append(" and TO_DAYS(b.create_date) >= TO_DAYS('" + dateNowFirst + "')");
            }

            if (dto.getqEndDate() != null&&StringUtils.hasText(dto.getqEndDate())) {
                sql.append(" and TO_DAYS(b.create_date) <= TO_DAYS('" + dto.getqEndDate() + "')");
            }else{
            	sql.append(" and TO_DAYS(b.create_date) <= TO_DAYS('" + dateNowLast + "')");
            }

            sql.append(" order by b.create_date desc");
        }
        dto.getPager().setPageSize(10);
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryWithPagingJpaCallback<RemindDto>(sql.toString(),
                "select count(1) from (" + sql.toString() + ") x", dto.getPager()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
                RemindDto remindDto = new RemindDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(obj[1].toString());
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindMsg(String.valueOf(obj[19]));
                try {
                    remindDto.setRemindDate(new SimpleDateFormat("yyyy-MM-dd").parse(String
                        .valueOf(obj[20])));
                } catch (ParseException e) {
                    throw new AppException("SimpleDateFormat", "日期转换错误！");
                }
                remindDtos.add(remindDto);
            }
        } else {
            remindDtos = null;
        }
        return remindDtos;
    }

    /**
     * {@inheritDoc} overridden:
     * 
     * @Date : Dec 2, 2015
     * @see com.cap.bts.crmdp.dealer.remind.dao.IRemindDao#getRemindHistoryList(com.cap.bts.crmdp.dealer.remind.dto.RemindDto)
     **/
    @Override
    public List<RemindDto> getRemindHistoryListByUserAndServiceType(RemindDto dto)
        throws AppException {
        List<RemindDto> remindDtos = new ArrayList<RemindDto>();
        List<ServiceTypeDto> tempServiceTypeDtos = dto.getServiceType();
        List<ServiceTypeDto> serviceTypeDtos = new ArrayList<ServiceTypeDto>();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, b.message_content,b.create_date"
            + " FROM"
            + " vt_service_remind a"
            + " INNER JOIN tm_service_remind_send_history b"
            + " where a.dealer_num='" + UserUtil.getCurrentUser().getDealerId() + "' ");
        StringBuilder whereString = new StringBuilder();
        if (tempServiceTypeDtos != null) {
            for (ServiceTypeDto serviceTypeDto : tempServiceTypeDtos) {
                if (serviceTypeDto.getServiceFlag()) {
                    serviceTypeDtos.add(serviceTypeDto);
                }
            }

            if (!serviceTypeDtos.isEmpty()) {
                for (ServiceTypeDto serviceTypeDto : serviceTypeDtos) {
                    whereString.append("'" + serviceTypeDto.getServiceType() + "',");
                }
                whereString.deleteCharAt(whereString.length() - 1);
                sql.append("and a.maintenance_item in (" + whereString.toString() + ")");
            }
        }

        /**
         *  SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        // X天后日期
        java.util.Date myDate = new java.util.Date();
        String dateNowString = formatter.format(myDate);

        CodeInfo afterRemindDays = codeMapping.getCodeInfoByCode("AfterRemindDays");

        sql.append(" and TO_DAYS(a.next_calc_date) < TO_DAYS('" + dateNowString + "') - "
            + afterRemindDays.getValue());
        *
        **/

        if (dto.getqHMemberName() != null) {
            sql.append(" and a.member_name  like '%" + dto.getqHMemberName() + "%'");
        }

        if (dto.getqHCarPlate() != null) {
            sql.append(" and a.car_plate  like '%" + dto.getqHCarPlate() + "%'");
        }

        if (dto.getqHMobliePhone() != null) {
            sql.append(" and a.mobile_phone  like '%" + dto.getqHMobliePhone() + "%'");
        }

        if (dto.getqStartDate() != null) {
            sql.append(" and TO_DAYS(b.create_date) >= TO_DAYS('" + dto.getqStartDate() + "')");
        }

        if (dto.getqEndDate() != null) {
            sql.append(" and TO_DAYS(b.create_date) <= TO_DAYS('" + dto.getqEndDate() + "')");
        }

        sql.append(" order by b.create_date desc");

        dto.getPager().setPageSize(10);
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryWithPagingJpaCallback<RemindDto>(sql.toString(),
                "select count(1) from (" + sql.toString() + ") x", dto.getPager()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
                RemindDto remindDto = new RemindDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(obj[1].toString());
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindMsg(String.valueOf(obj[19]));
                try {
                    remindDto.setRemindDate(new SimpleDateFormat("yyyy-MM-dd").parse(String
                        .valueOf(obj[20])));
                } catch (ParseException e) {
                    throw new AppException("SimpleDateFormat", "日期转换错误！");
                }
                remindDtos.add(remindDto);
            }
        } else {
            remindDtos = null;
        }
        return remindDtos;
    }

	@Override
	public List<RemindDto> getRemindListByCarPlate(RemindDto dto) throws AppException {
        List<RemindDto> remindDtos = new ArrayList<RemindDto>();
        dto.getPager().setPageSize(20);
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, count(c.create_date) times  from vt_service_remind a "
            + " left join tm_service_remind_send_history c on a.id=c.service_remind_id  and c.status='1' and c.dealer_id='"
            + UserUtil.getCurrentUser().getDealerId()
            + "' where a.dealer_num='"
            + UserUtil.getCurrentUser().getDealerId() + "'"
        	+ " AND a.car_plate = '" + dto.getCarPlate() + "'");
        sql.append(" group by a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage");
        StringBuilder sqlAll = new StringBuilder();
        StringBuilder sqlCount = new StringBuilder();
        sqlCount.append("select count(1) from(" + sql + ") a");
        sqlAll.append(sql.toString());
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryWithPagingJpaCallback(sqlAll.toString(), sqlCount.toString(), dto.getPager()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
                RemindDto remindDto = new RemindDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(obj[1].toString());
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindTimes(Integer.valueOf(String.valueOf(obj[19])));
                remindDtos.add(remindDto);
            }

        } else {
            remindDtos = null;
        }
        return remindDtos;
    }

	@Override
	public List<ServiceRemindForMemberDto> getRemindListByMemberNameOrMoblieOrCarPlate(ServiceRemindForMemberDto dto)
			throws AppException {
        List<ServiceRemindForMemberDto> remindDtos = new ArrayList<ServiceRemindForMemberDto>();
        String maintenanceItem = dto.getMaintenanceItem();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, " +
            " (select count(*) from tm_service_remind_send_history c where c.service_remind_id = a.id and c. STATUS = '1' ");
        sql.append(" )  times,  a.last_calc_date,a.count_of_to_store,a.money_of_year,a.last_remind_date from vt_service_remind a  where a.dealer_num='"
            + UserUtil.getCurrentUser().getDealerId() + "' ");
        StringBuilder whereString = new StringBuilder();
        if (maintenanceItem != null && !"".equals(maintenanceItem)) {
        	whereString.append("'" + maintenanceItem + "',");
        	whereString.deleteCharAt(whereString.length() - 1);
        	sql.append(" and a.maintenance_item in (" + whereString.toString() + ")");
        }else{
        	List<CodeInfo> codelist = codeMapping.getCodeInfoByType("ServiceRemind");
            if (codelist != null && !codelist.isEmpty()) {
            whereString.append("'" + codelist.get(0).getValue() + "'");
            sql.append("and a.maintenance_item in (" + whereString.toString() + ")");
            }
        }
        if(dto.getCountOfToStoreButtom() != null && !"".equals(dto.getCountOfToStoreButtom())){
        	sql.append(" and a.count_of_to_store >=" + dto.getCountOfToStoreButtom());
        }
        if(dto.getCountOfToStoreTop() != null && !"".equals(dto.getCountOfToStoreTop())){
        	sql.append(" and a.count_of_to_store <=" + dto.getCountOfToStoreTop());
        }
        if(dto.getMoneyButtom() != null && !"".equals(dto.getMoneyButtom())){
        	sql.append(" and a.money_of_year >=" + dto.getMoneyButtom());
        }
        if(dto.getMoneyTop() != null && !"".equals(dto.getMoneyTop())){
        	sql.append(" and a.money_of_year <=" + dto.getMoneyTop());
        }
        String carModel = dto.getCarModel();
        if(!"".equals(carModel) && carModel != null && !"-1".equals(carModel)){
        	sql.append(" and a.car_model = '" + carModel + "'");
        }
        String groupName = dto.getGroupName();
        if(!"".equals(groupName) && groupName != null && !"-1".equals(groupName) && !"-2".equals(groupName)){
        	if("-3".equals(groupName)){//潜在客户
        		sql.append(" and a.last_maintenance_store != '" + UserUtil.getCurrentUser().getDealerId() + "'");
        	}else if("-4".equals(groupName)){//重复消费客户
        		sql.append(" and a.last_maintenance_store = '" + UserUtil.getCurrentUser().getDealerId() + "'");
        	}else{
        		//根据客户分组获取到顾客id
        		List<TrCustomerGroup> trCustomerGroupList = trCustomerGroupDao.getTrCustomerGroupByDealerIdAndGroupName(UserUtil.getCurrentUser().getDealerId(), groupName);
        		if(trCustomerGroupList != null && !trCustomerGroupList.isEmpty()){
        			StringBuilder customerId = new StringBuilder();
        			for(TrCustomerGroup trCustomerGroup : trCustomerGroupList){
        				customerId.append("'" + trCustomerGroup.getCustomer_id() + "',");
        			}
        			customerId.deleteCharAt(customerId.length() - 1);
        			sql.append(" and a.customer_id in (" + customerId + ")");
        		}
        	}
        }
        
        sql.append(" group by a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage,a.last_calc_date,a.count_of_to_store,a.money_of_year,a.last_remind_date");
        StringBuilder sqlBuilder=new StringBuilder();
        sqlBuilder.append("select * from ("+sql.toString() + ") x"
    			+ " ORDER BY convert(x.member_name USING gb2312),convert(x.car_plate USING gb2312),x.next_calc_date");
        LOGGER.info("sql#############:" + sqlBuilder.toString());
        
        dto.getPager().setPageSize(5);
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryWithPagingJpaCallback<Object[]>(sqlBuilder.toString(),
                "select count(1) from (" + sqlBuilder.toString() + ") y", dto.getPager()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
            	ServiceRemindForMemberDto remindDto = new ServiceRemindForMemberDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(Utils.getString(obj[1]));
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindTimes(Integer.valueOf(String.valueOf(obj[19])));
                remindDto.setLastCalcDate(String.valueOf(obj[20]));
                if("".equals(obj[21]) || obj[21] == null){
                	remindDto.setCountOfToStore("");
                }else{
                	remindDto.setCountOfToStore(String.valueOf(obj[21]));
                }
                if("".equals(obj[22]) || obj[22] == null){
                	remindDto.setMoneyOfYear("");
                }else{
                	remindDto.setMoneyOfYear(String.valueOf(obj[22]));
                }
                if("".equals(obj[23]) || obj[23] == null){
                	remindDto.setLastRemindDate("");
                }else{
                	remindDto.setLastRemindDate(String.valueOf(obj[23]));
                }
                remindDtos.add(remindDto);
            }
        } else {
            remindDtos = null;
        }
        return remindDtos;
    }

	@Override
	public List<ServiceRemindForMemberDto> getRemindListByMemberNameOrMoblieOrCarPlateNoPage(ServiceRemindForMemberDto dto) throws AppException {
        List<ServiceRemindForMemberDto> remindDtos = new ArrayList<ServiceRemindForMemberDto>();
        String maintenanceItem = dto.getMaintenanceItem();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, " +
                " (select count(*) from tm_service_remind_send_history c where c.service_remind_id = a.id and c. STATUS = '1' ");
            sql.append(" )  times,  a.last_calc_date,a.count_of_to_store,a.money_of_year,a.last_remind_date from vt_service_remind a  where a.dealer_num='"
                + UserUtil.getCurrentUser().getDealerId() + "' ");
        StringBuilder whereString = new StringBuilder();
        if (maintenanceItem != null && !"".equals(maintenanceItem)) {
        	whereString.append("'" + maintenanceItem + "',");
        	whereString.deleteCharAt(whereString.length() - 1);
        	sql.append(" and a.maintenance_item in (" + whereString.toString() + ")");
        }else{
        	List<CodeInfo> codelist = codeMapping.getCodeInfoByType("ServiceRemind");
            if (codelist != null && !codelist.isEmpty()) {
            whereString.append("'" + codelist.get(0).getValue() + "'");
            sql.append("and a.maintenance_item in (" + whereString.toString() + ")");
            }
        }
        if(dto.getCountOfToStoreButtom() != null && !"".equals(dto.getCountOfToStoreButtom())){
        	sql.append(" and a.count_of_to_store >=" + dto.getCountOfToStoreButtom());
        }
        if(dto.getCountOfToStoreTop() != null && !"".equals(dto.getCountOfToStoreTop())){
        	sql.append(" and a.count_of_to_store <=" + dto.getCountOfToStoreTop());
        }
        if(dto.getMoneyButtom() != null && !"".equals(dto.getMoneyButtom())){
        	sql.append(" and a.money_of_year >=" + dto.getMoneyButtom());
        }
        if(dto.getMoneyTop() != null && !"".equals(dto.getMoneyTop())){
        	sql.append(" and a.money_of_year <=" + dto.getMoneyTop());
        }
        String carModel = dto.getCarModel();
        if(!"".equals(carModel) && carModel != null && !"-1".equals(carModel)){
        	sql.append(" and a.car_model = '" + carModel + "'");
        }
        String groupName = dto.getGroupName();
        if(!"".equals(groupName) && groupName != null && !"-1".equals(groupName)){
        	//根据客户分组获取到顾客id
        	List<TrCustomerGroup> trCustomerGroupList = trCustomerGroupDao.getTrCustomerGroupByDealerIdAndGroupName(UserUtil.getCurrentUser().getDealerId(), groupName);
        	if(trCustomerGroupList != null && !trCustomerGroupList.isEmpty()){
        		StringBuilder customerId = new StringBuilder();
        		for(TrCustomerGroup trCustomerGroup : trCustomerGroupList){
        			customerId.append("'" + trCustomerGroup.getCustomer_id() + "',");
        		}
        		customerId.deleteCharAt(customerId.length() - 1);
        		sql.append(" and a.customer_id in (" + customerId + ")");
        	}
        	sql.append(" and a.car_model = '" + carModel + "'");
        }
        StringBuilder sqlAll = new StringBuilder();
        sqlAll.append("select * from ("+sql.toString()+") x"
    			+ " ORDER BY convert(x.member_name USING gb2312),convert(x.car_plate USING gb2312),x.next_calc_date");
        LOGGER.info("sqlforAll#############:" + sqlAll.toString());
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryBySqlJpaCallback<Object[]>(sqlAll.toString()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
            	ServiceRemindForMemberDto remindDto = new ServiceRemindForMemberDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(obj[1].toString());
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindTimes(Integer.valueOf(String.valueOf(obj[19])));
                remindDtos.add(remindDto);
            }

        } else {
            remindDtos = null;
        }
        return remindDtos;
    }

	@Override
	public List<ServiceRemindHistoryDto> getServiceRemindHistoryByMobilePhone(ServiceRemindForMemberDto dto) throws AppException {
		String sql = "select mobile_phone,message_content,create_date,return_date from vt_service_remind_send_history where mobile_phone = '" + dto.getMobliePhone() + "'"
				+ " and dealer_num = '" + dto.getDealerNum() + "'";
		List<Object[]> list = super.getJpaTemplate().executeFind(
	            new NativeQueryWithPagingJpaCallback<Object[]>(sql,
	                    "select count(1) from (" + sql + ") y", dto.getPager()));
		List<ServiceRemindHistoryDto> dtolist = new ArrayList<ServiceRemindHistoryDto>();
		if(list != null && !list.isEmpty()){
			for (Object[] obj : list) {
				ServiceRemindHistoryDto srDto = new ServiceRemindHistoryDto();
				srDto.setMobliePhone(String.valueOf(obj[0]));
				srDto.setMessageContent(String.valueOf(obj[1]));
				srDto.setRemindDate(String.valueOf(obj[2]));
				if("".equals(obj[3]) || obj[3] == null){
					srDto.setReturnDate("");
                }else{
                	srDto.setReturnDate(String.valueOf(obj[3]));
                }
				dtolist.add(srDto);
			}
		}
		dto.getPager().setPageSize(10);
		return dtolist;
	}

	@Override
	public List<ServiceRemindForMemberDto> getRemindListForCreateCustomerGroup(ServiceRemindForMemberDto dto)
			throws AppException {
        List<ServiceRemindForMemberDto> remindDtos = new ArrayList<ServiceRemindForMemberDto>();
        String maintenanceItem = dto.getMaintenanceItem();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, " +
            " (select count(*) from tm_service_remind_send_history c where c.service_remind_id = a.id and c. STATUS = '1' ");
        sql.append(" )  times,  a.last_calc_date,a.count_of_to_store,a.money_of_year,a.last_remind_date,a.customer_id from vt_service_remind a  where a.dealer_num='"
            + UserUtil.getCurrentUser().getDealerId() + "' ");
        if(dto.getCountOfToStoreButtom() != null && !"".equals(dto.getCountOfToStoreButtom())){
        	sql.append(" and a.count_of_to_store >=" + dto.getCountOfToStoreButtom());
        }
        if(dto.getCountOfToStoreTop() != null && !"".equals(dto.getCountOfToStoreTop())){
        	sql.append(" and a.count_of_to_store <=" + dto.getCountOfToStoreTop());
        }
        if(dto.getMoneyButtom() != null && !"".equals(dto.getMoneyButtom())){
        	sql.append(" and a.money_of_year >=" + dto.getMoneyButtom());
        }
        if(dto.getMoneyTop() != null && !"".equals(dto.getMoneyTop())){
        	sql.append(" and a.money_of_year <=" + dto.getMoneyTop());
        }
        String carModel = dto.getCarModel();
        if(!"".equals(carModel) && carModel != null && !"-1".equals(carModel)){
        	sql.append(" and a.car_model = '" + carModel + "'");
        }
        String groupName = "全部";
        if(!"".equals(groupName) && groupName != null && !"-1".equals(groupName) && !"-2".equals(groupName)){
        	//根据客户分组获取到顾客id
        	List<TrCustomerGroup> trCustomerGroupList = trCustomerGroupDao.getTrCustomerGroupByDealerIdAndGroupName(UserUtil.getCurrentUser().getDealerId(), groupName);
        	if(trCustomerGroupList != null && !trCustomerGroupList.isEmpty()){
        		StringBuilder customerId = new StringBuilder();
        		for(TrCustomerGroup trCustomerGroup : trCustomerGroupList){
        			customerId.append("'" + trCustomerGroup.getCustomer_id() + "',");
        		}
        		customerId.deleteCharAt(customerId.length() - 1);
        		sql.append(" and a.customer_id in (" + customerId + ")");
        	}
        }
        sql.append(" group by a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage,a.last_calc_date,a.count_of_to_store,a.money_of_year,a.last_remind_date");
        StringBuilder sqlBuilder=new StringBuilder();
        sqlBuilder.append("select * from ("+sql.toString() + ") x"
    			+ " ORDER BY convert(x.member_name USING gb2312),convert(x.car_plate USING gb2312),x.next_calc_date");
        LOGGER.info("sql#############:" + sqlBuilder.toString());
        
        dto.getPager().setPageSize(5);
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryWithPagingJpaCallback<Object[]>(sqlBuilder.toString(),
                "select count(1) from (" + sqlBuilder.toString() + ") y", dto.getPager()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
            	ServiceRemindForMemberDto remindDto = new ServiceRemindForMemberDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(Utils.getString(obj[1]));
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindTimes(Integer.valueOf(String.valueOf(obj[19])));
                remindDto.setLastCalcDate(String.valueOf(obj[20]));
                if("".equals(obj[21]) || obj[21] == null){
                	remindDto.setCountOfToStore("");
                }else{
                	remindDto.setCountOfToStore(String.valueOf(obj[21]));
                }
                if("".equals(obj[22]) || obj[22] == null){
                	remindDto.setMoneyOfYear("");
                }else{
                	remindDto.setMoneyOfYear(String.valueOf(obj[22]));
                }
                if("".equals(obj[23]) || obj[23] == null){
                	remindDto.setLastRemindDate("");
                }else{
                	remindDto.setLastRemindDate(String.valueOf(obj[23]));
                }
                remindDto.setMemberId(String.valueOf(obj[24]));
                remindDtos.add(remindDto);
            }
        } else {
            remindDtos = null;
        }
        return remindDtos;
    }

	@Override
	public List<ServiceRemindForMemberDto> getRemindListForNotice(ServiceRemindForMemberDto dto) throws AppException {
        List<ServiceRemindForMemberDto> remindDtos = new ArrayList<ServiceRemindForMemberDto>();
        String maintenanceItem = dto.getMaintenanceItem();
        StringBuilder sql = new StringBuilder();
        sql.append("select a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage, " +
            " (select count(*) from tm_service_remind_send_history c where c.service_remind_id = a.id and c. STATUS = '1' ");
        sql.append(" )  times,  a.last_calc_date,a.count_of_to_store,a.money_of_year,a.last_remind_date from vt_service_remind a  where a.dealer_num='"
            + UserUtil.getCurrentUser().getDealerId() + "' ");
        if(dto.getCountOfToStoreButtom() != null && !"".equals(dto.getCountOfToStoreButtom())){
        	sql.append(" and a.count_of_to_store >=" + dto.getCountOfToStoreButtom());
        }
        if(dto.getCountOfToStoreTop() != null && !"".equals(dto.getCountOfToStoreTop())){
        	sql.append(" and a.count_of_to_store <=" + dto.getCountOfToStoreTop());
        }
        if(dto.getMoneyButtom() != null && !"".equals(dto.getMoneyButtom())){
        	sql.append(" and a.money_of_year >=" + dto.getMoneyButtom());
        }
        if(dto.getMoneyTop() != null && !"".equals(dto.getMoneyTop())){
        	sql.append(" and a.money_of_year <=" + dto.getMoneyTop());
        }
        String carModel = dto.getCarModel();
        if(!"".equals(carModel) && carModel != null && !"-1".equals(carModel)){
        	sql.append(" and a.car_model = '" + carModel + "'");
        }
        String groupName = dto.getGroupName();
        if(!"".equals(groupName) && groupName != null && !"-1".equals(groupName)){
        	//根据客户分组获取到顾客id
        	List<TrCustomerGroup> trCustomerGroupList = trCustomerGroupDao.getTrCustomerGroupByDealerIdAndGroupName(UserUtil.getCurrentUser().getDealerId(), groupName);
        	if(trCustomerGroupList != null && !trCustomerGroupList.isEmpty()){
        		StringBuilder customerId = new StringBuilder();
        		for(TrCustomerGroup trCustomerGroup : trCustomerGroupList){
        			customerId.append("'" + trCustomerGroup.getCustomer_id() + "',");
        		}
        		customerId.deleteCharAt(customerId.length() - 1);
        		sql.append(" and a.customer_id in (" + customerId + ")");
        	}
        }
        
        sql.append(" group by a.dealer_num,a.mobile_phone,a.member_name,a.id,a.car_plate,a.car_model,a.maintenance_item,a.last_part_brand,a.last_part_name,a.last_maintenance_store,a.last_maintenance_date,a.last_maintenance_mileage,a.last_carcheck_store,a.last_carcheck_date,a.last_carcheck_mileage,a.next_calc_basis,a.maintenance_spec_basis,a.next_calc_date,a.next_calc_mileage,a.last_calc_date,a.count_of_to_store,a.money_of_year,a.last_remind_date");
        StringBuilder sqlBuilder=new StringBuilder();
        sqlBuilder.append("select * from ("+sql.toString() + ") x"
    			+ " ORDER BY convert(x.member_name USING gb2312),convert(x.car_plate USING gb2312),x.next_calc_date");
        LOGGER.info("sql#############:" + sqlBuilder.toString());
        
        dto.getPager().setPageSize(5);
        List<Object[]> list = super.getJpaTemplate().executeFind(
            new NativeQueryWithPagingJpaCallback<Object[]>(sqlBuilder.toString(),
                "select count(1) from (" + sqlBuilder.toString() + ") y", dto.getPager()));
        if (list != null && !list.isEmpty()) {
            for (Object[] obj : list) {
            	ServiceRemindForMemberDto remindDto = new ServiceRemindForMemberDto();
                remindDto.setDealerNum(obj[0].toString());
                remindDto.setMobliePhone(Utils.getString(obj[1]));
                remindDto.setMemberName(obj[2].toString());
                remindDto.setId(obj[3].toString());
                remindDto.setCarPlate((String) obj[4]);
                remindDto.setCarModel((String) obj[5]);
                remindDto.setMaintenanceItem((String) obj[6]);
                remindDto.setLastPartBrand((String) obj[7]);
                remindDto.setLastPartName((String) obj[8]);
                remindDto.setLastMaintenanceStore((String) obj[9]);
                remindDto.setLastMaintenanceDate(String.valueOf(obj[10]));
                remindDto.setLastMaintenanceMileage(String.valueOf(obj[11]));
                remindDto.setLastCarcheckStore(String.valueOf(obj[12]));
                remindDto.setLastCarcheckDate(String.valueOf(obj[13]));
                remindDto.setLastCarcheckMileage(String.valueOf(obj[14]));
                remindDto.setNextCalcbasis((String) obj[15]);
                remindDto.setMaintenanceSpecBasis((String) obj[16]);
                remindDto.setNextCalcDate(String.valueOf(obj[17]));
                remindDto.setNextCalcMileage(String.valueOf(obj[18]));
                remindDto.setRemindTimes(Integer.valueOf(String.valueOf(obj[19])));
                remindDto.setLastCalcDate(String.valueOf(obj[20]));
                if("".equals(obj[21]) || obj[21] == null){
                	remindDto.setCountOfToStore("");
                }else{
                	remindDto.setCountOfToStore(String.valueOf(obj[21]));
                }
                if("".equals(obj[22]) || obj[22] == null){
                	remindDto.setMoneyOfYear("");
                }else{
                	remindDto.setMoneyOfYear(String.valueOf(obj[22]));
                }
                if("".equals(obj[23]) || obj[23] == null){
                	remindDto.setLastRemindDate("");
                }else{
                	remindDto.setLastRemindDate(String.valueOf(obj[23]));
                }
                remindDtos.add(remindDto);
            }
        } else {
            remindDtos = null;
        }
        return remindDtos;
    }


}
